package com.zzc.dao;

import com.google.gson.Gson;
import com.zzc.bean.DiningReceipt;
import com.zzc.bean.DiningReceipt_sum;
import com.zzc.bean.DiningReceipt_sum_sql;
import com.zzc.bean.DiningReceipt_sum_sql_nocompany;
import org.hibernate.Query;
import org.hibernate.transform.Transformers;

import java.util.Date;
import java.util.Iterator;
import java.util.List;

public class DiningReceipt_sumDao extends BaseDao{

	public List<DiningReceipt_sum> search_diningReceipt(String employee_account, String dining_company, String dining_type) {
		String hql = "FROM DiningReceipt_sum diningReceipt_sum WHERE diningReceipt_sum.employee_account=? and diningReceipt_sum.dining_company=? and diningReceipt_sum.dining_type=? ";//hql = "FROM DiningReceipt";
		Query query = getSession().createQuery(hql).setString(0, employee_account).setString(1,dining_company).setString(2,dining_type);
		List<DiningReceipt_sum> list_diningReceipts =query.list();
		return list_diningReceipts;
	}
	public List<?> getDiningReceipt_sum_list(String string_timeStamp_filter) {
		String hql = "select dining_company,convert(varchar(10),dining_time,21) as dining_time,dining_type,sum(cast(employee_number as int)) as employee_number,sum(cast(leadership_number as int)) as leadership_number from DiningReceipt ";//hql = "FROM DiningReceipt";
		if (string_timeStamp_filter!=null){
			Long long_timeStamp_filter=Long.parseLong(string_timeStamp_filter);
			Date date_filter=new Date(long_timeStamp_filter);
			java.sql.Date sqlDate=new java.sql.Date(date_filter.getTime());
			hql=hql+"where 1=1 and dining_time>="+"CONVERT(varchar(10),'"+ sqlDate.toLocalDate()+"',21) ";
		}
		hql=hql+"group by dining_company,convert(varchar(10),dining_time,21),dining_type order by convert(varchar(10),dining_time,21)DESC,dining_type";
		Query query = getSession().createSQLQuery(hql).setResultTransformer(Transformers.aliasToBean(DiningReceipt_sum_sql.class));
//		List<DiningReceipt_sum_sql> list = getSession().createSQLQuery(hql2).setResultTransformer(Transformers.aliasToBean(DiningReceipt_sum_sql.class)).list();
		List<DiningReceipt_sum_sql>  list = query.list();
//		List<DiningReceipt_sum_sql> list2= getSession().createSQLQuery(hql2).setResultTransformer(Transformers.aliasToBean(DiningReceipt_sum_sql.class)).list();
//		System.out.println("开始枚举list");
//		for(Iterator iterator = list.iterator(); iterator.hasNext();){
//			//每个集合元素都是Person、MyEvent所组成的数组
//			Object[] objects = (Object[]) iterator.next();
//			System.out.println("开始枚举objects");
//			for (int i=0;i<objects.length;i++){
//				System.out.println(" "+objects[i]);
//			}
//			System.out.println("结束枚举objects");
//		}
		System.out.println("结束枚举list");
		for(DiningReceipt_sum_sql diningReceipt_sum_sql : list){
			System.out.println(diningReceipt_sum_sql.getDining_time());
		}
		return list;

//		return list_diningReceipt_sums;
	}
	public List<?> getDiningReceipt_sum_list_nodining_company(String string_timeStamp_filter) {
		String hql = "select   convert(varchar(10),dining_time,21) as dining_time,dining_type,sum(cast(employee_number as int)) as employee_number,sum(cast(leadership_number as int)) as leadership_number from DiningReceipt group by convert(varchar(10),dining_time,21),dining_type order by convert(varchar(10),dining_time,21)DESC,dining_type";//hql = "FROM DiningReceipt";
		Query query = getSession().createSQLQuery(hql).setResultTransformer(Transformers.aliasToBean(DiningReceipt_sum_sql_nocompany.class));
		List<DiningReceipt_sum_sql_nocompany>  list = query.list();
		System.out.println("结束枚举list");
		for(DiningReceipt_sum_sql_nocompany diningReceipt_sum_sql : list){
			System.out.println(diningReceipt_sum_sql.getDining_time());
		}
		return list;
	}
	public List<?> getDiningReceipt_sum_list_employee_account(String employee_account) {
//		String hql = "FROM DiningReceipt_sum diningReceipt_sum WHERE diningReceipt_sum.employee_account=?";//hql = "FROM DiningReceipt";
//		Query query = getSession().createQuery(hql).setString(0, employee_account);
//		List<DiningReceipt> list_diningReceipt_sums =query.list();
//		return list_diningReceipt_sums;
		String hql = "select dining_company,convert(varchar(10),dining_time,21) as dining_time,dining_type,sum(cast(employee_number as int)) as employee_number,sum(cast(leadership_number as int)) as leadership_number from DiningReceipt diningReceipt WHERE diningReceipt.employee_account=? group by dining_company,convert(varchar(10),dining_time,21),dining_type order by convert(varchar(10),dining_time,21)DESC,dining_type";//hql = "FROM DiningReceipt";
		Query query = getSession().createSQLQuery(hql).setString(0,employee_account).setResultTransformer(Transformers.aliasToBean(DiningReceipt_sum_sql.class));
		List list = query.list();
		return list;
	}
	public List<?> getDiningReceipt_sum_list_employee_account_nodining_company(String employee_account) {
		String hql = "select convert(varchar(10),dining_time,21) as dining_time,dining_type,sum(cast(employee_number as int)) as employee_number,sum(cast(leadership_number as int)) as leadership_number from DiningReceipt diningReceipt WHERE diningReceipt.employee_account=? group by convert(varchar(10),dining_time,21),dining_type order by convert(varchar(10),dining_time,21)DESC,dining_type";//hql = "FROM DiningReceipt";
		Query query = getSession().createSQLQuery(hql).setString(0,employee_account).setResultTransformer(Transformers.aliasToBean(DiningReceipt_sum_sql_nocompany.class));
		List list = query.list();
		return list;
	}
}
